package yf.mall.utils.app;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.formula.functions.Slope;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;

import java.sql.*;

/**
 * @author yxmds
 */
@Slf4j
public class TableBackup {
    public static long backupTable(DataSourceProperties properties, String tableName, String backupTableName, String sqlWhere) {
        log.info("开始备份表，tableName={}，backupTableName={}，sqlWhere={}", tableName, backupTableName, sqlWhere);

        // SQL语句用于插入数据到备份表
        String insertDataSQL = "INSERT IGNORE INTO " + backupTableName + " SELECT * FROM " + tableName + " where 1=1 " + sqlWhere + " LIMIT ?, ?";
        log.info("插入sql为：{}", insertDataSQL);
        int successCount = 0;
        try {
            // 连接到数据库
            Connection connection = DriverManager.getConnection(properties.getUrl(), properties.getUsername(), properties.getPassword());
            connection.setAutoCommit(false); // 设置手动提交

            // 检查备份表是否已存在
            if (!doesTableExist(connection, backupTableName)) {
                // 备份表不存在，创建备份表
                createBackupTable(connection, tableName, backupTableName);
            }

            // 查询原表数据量
            String countQuery = "SELECT COUNT(*) FROM " + tableName + " where 1=1 " + sqlWhere;
            PreparedStatement countStatement = connection.prepareStatement(countQuery);
            long rowCount = 0;
            if (countStatement.execute()) {
                rowCount = countStatement.getResultSet().next() ? countStatement.getResultSet().getLong(1) : 0;
            }

            // 插入数据到备份表
            // 批量插入的大小
            int batchSize = 100;
            long offset = 0;

            while (offset < rowCount) {
                // SQL语句用于插入数据到备份表
                PreparedStatement insertDataStatement = connection.prepareStatement(insertDataSQL);
                insertDataStatement.setLong(1, offset);
                insertDataStatement.setInt(2, batchSize);
                successCount += insertDataStatement.executeUpdate();
                offset += batchSize;
                if (!connection.getAutoCommit()) {
                    connection.commit(); // 提交每个批次的插入
                }
            }

            // 关闭数据库连接
            connection.close();

            log.info("备份表创建成功并数据插入完成，成功数={}", successCount);
            return successCount;
        } catch (SQLException e) {
            e.printStackTrace();
            log.error("备份表创建和数据插入过程中出现错误。");
        }
        return successCount;
    }

    // 检查表是否存在
    private static boolean doesTableExist(Connection connection, String tableName) throws SQLException {
        DatabaseMetaData meta = connection.getMetaData();
        ResultSet tables = meta.getTables(null, null, tableName, new String[]{"TABLE"});
        return tables.next();
    }

    // 创建备份表
    private static void createBackupTable(Connection connection, String sourceTableName, String backupTableName) throws SQLException {
        String createBackupTableSQL = "CREATE TABLE " + backupTableName + " AS SELECT * FROM " + sourceTableName + " WHERE 1=0";
        Statement createTableStatement = connection.createStatement();
        createTableStatement.execute(createBackupTableSQL);

        // 添加唯一索引
        String createIndexSQL = "ALTER TABLE " + backupTableName + " ADD UNIQUE INDEX idx_id (id)";
        Statement createIndexStatement = connection.createStatement();
        createIndexStatement.execute(createIndexSQL);
    }

    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/dx?useSSL=false"; // 替换为你的数据库连接
        String user = "yxmds"; // 替换为你的用户名
        String password = "123456"; // 替换为你的密码
        String tableName = "account"; // 替换为你要备份的表名
        String backupTableName = "account_back_20230930"; // 替换为备份表名
        DataSourceProperties properties = new DataSourceProperties();
        properties.setUrl(url);
        properties.setUsername(user);
        properties.setPassword(password);
        backupTable(properties, tableName, backupTableName, " and id > 100");
    }
}
